package br.com.banco.dao;

import br.com.banco.model.Conta;
import br.com.banco.model.Movimentacao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ContaDAO {

	Connection conexao;

	private void obterConexao() throws Exception {
		BancoDBManager conexaoBanco = new BancoDBManager();
		// this.conexao = conexaoBanco.obterConexaoMySQL();
        this.conexao = conexaoBanco.obterConexaoJavaDb();
	}

	private void fecharConexao() throws Exception {
		this.conexao.close();
	}

	public Conta retreive(int agencia, int conta) throws Exception {

		obterConexao();

		String sql = "SELECT id, agencia, conta, digito, saldo, idcliente FROM conta "
				+ "WHERE agencia = ? AND conta = ? AND digito = ? ";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, agencia);
		stmt.setInt(2, conta);

		ResultSet rs = stmt.executeQuery();

		Conta model = null;
		if (rs.next()) {
			model = new Conta();
			model.setId(rs.getInt("id"));
			model.setAgencia(rs.getInt("agencia"));
			model.setNumero(rs.getInt("conta"));
			model.setSaldo(rs.getDouble("saldo"));
		}

		fecharConexao();
		return model;

	}

	public Conta retreiveMovimentos(Conta conta) {

		Conta entity = null;
		try {
			obterConexao();

			List<Movimentacao> movimentacoes = new ArrayList<Movimentacao>();

			entity = this.retreive(conta.getAgencia(), conta.getNumero());

			String sql = "SELECT id, tipomovto, valor, data, idconta FROM movimentacao "
					+ "WHERE idconta = ? ";

			PreparedStatement stmt = conexao.prepareStatement(sql);
			stmt.setInt(1, conta.getId());

			ResultSet rs = stmt.executeQuery();

			while (true) {
				if (rs.next()) {
					Movimentacao movto = new Movimentacao();
					movto.setId(rs.getInt("id"));
					movto.setTipoMovto(rs.getString("tipomovto"));
					movto.setValor(rs.getDouble("valor"));
					movto.setData(rs.getDate("data"));
					movto.setIdConta(rs.getInt("idconta"));
					movimentacoes.add(movto);
				} else {
					break;
				}
			}

			entity.setMovimento(movimentacoes);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fecharConexao();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return entity;

	}

	public boolean create(Conta conta, double valorInicial) {

		int count = 0;
		try {
			obterConexao();

			String sql = "INSERT INTO conta (agencia,conta,digito,saldo,idcliente) VALUES (?,?,?,?,?) ";
			PreparedStatement stmt = conexao.prepareStatement(sql);
			stmt.setInt(1, conta.getAgencia());
			stmt.setInt(2, conta.getNumero());
			stmt.setDouble(4, valorInicial);
			count = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fecharConexao();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return count > 0;

	}


}
